package com.cnsuning.jobtool.jobtools.sql;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class SqlUtil {
    private static String tableName = "SO_ORDI_PAY_INSTALMENT";

    private static Map<String,String> routemap = new HashMap<>(500);

    static {
        String routeFilePath = "src\\main\\java\\com\\cnsuning\\jobtool\\jobtools\\sql\\123.txt";
        File file = new File(routeFilePath);
        try {
            FileReader reader = new FileReader(file);
            BufferedReader bfReader = new BufferedReader(reader);
            String str = "";
            while((str = bfReader.readLine()) != null){
                String [] values = str.split(",");
                routemap.put(values[0],values[1]);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) throws IOException {
//        String path = "C:/Users/11060/Desktop/workinfo/sql/SO_ORDI_PAY_INSTALMENT.txt";
        test2();
    }

    private static void test2() throws IOException {
        String dir = "src\\main\\java\\com\\cnsuning\\jobtoole\\jobtools\\sql\\files";
        File file = new File(dir);
        File[] files = file.listFiles();
        for(File f : files){
            String filePath = f.getAbsolutePath();
            String fileName = f.getName();
            fileName = fileName.split("-")[1];
            int index = fileName.lastIndexOf(".");
            tableName = fileName.substring(0, index);
            System.out.println(tableName);
            test(filePath);
        }
    }

    private static void test(String path) throws IOException {

        String sqlPath = "D:/script";
        Map<String, Map<String,List<String>>> map = createSoDataDisInfo(path);
        createSqlFile(map, sqlPath);
    }

    /**
     * 读取数据，生成新增和删除sql
     *
     * @param filePath
     * @return
     */
    private static Map<String, Map<String,List<String>>> createSoDataDisInfo(String filePath) {
        Map<String, Map<String,List<String>>> script = new HashMap<>();
        Map<String,List<String>> createSqlScript = new HashMap<>();
        Map<String,List<String>> deleteSqlScript = new HashMap<>();
        String[] columns = null;
        try {
            FileReader reader = new FileReader(new File(filePath));
            BufferedReader bfReader = new BufferedReader(reader);
            String str = "";
            boolean header = true;
            while ((str = bfReader.readLine()) != null) {
                //第一行为字段
                if (header) {
                    str = str.replaceAll("\"","");
                    columns = str.split(",");
                    header = false;
                    continue;
                }
                if(str.indexOf("\"ROW_ID\"") != -1){
                    continue;
                }
                //处理数据
                //String[] values = str.split(",");
                String[] values = handleColumnValue(str);
                Map<String,String> map = intoMap(columns, values);
                String[] insertSql = createInsertSql(map);
                List<String> insertList = createSqlScript.get(insertSql[0]);
                if(insertList == null){
                    insertList = new LinkedList<>();
                    createSqlScript.put(insertSql[0], insertList);
                }
                insertList.add(insertSql[1]);
                String[] deleteSql = createDeleteSql(columns, values);
                List<String> deleteList = deleteSqlScript.get(deleteSql[0]);
                if(deleteList==null){
                    deleteList = new LinkedList<>();
                    deleteSqlScript.put(deleteSql[0], deleteList);
                }
                deleteList.add(deleteSql[1]);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        script.put("insert", createSqlScript);
        script.put("delete", deleteSqlScript);
        return script;
    }

    private static String[] handleColumnValue(String value){
        int startIndex = value.indexOf("\"{");
        int endIndex = value.lastIndexOf("}\",") +2;
        String json = null;
        if(startIndex != -1 && endIndex != -1){
            json = value.substring(startIndex, endIndex);
        }
        if(json == null){
            return value.split(",");
        }
        String str1 = value.substring(0, startIndex);
        String str2 = value.substring(endIndex, value.length());
        String [] strValues1 = null;
        String[] strValues2 = null;
        int index = 0;
        if(str1 != null){
            str1 = str1.substring(0, str1.length()-1);
            strValues1= str1.split(",");
        }

        if(str2 != null){
            str2 = str2.substring(1, str2.length());
            strValues2 = str2.split(",");
        }
        if(strValues1 != null){
            index += strValues1.length;
        }
        if(strValues2 != null){
            index += strValues2.length;
        }
        if(json != null){
            index ++;
        }
        String[] all = new String[index];
        int dataIndex = 0;
        if(strValues1 != null){
            for(int i = 0; i<strValues1.length; i++){
                all[dataIndex] = strValues1[i];
                dataIndex++;
            }
        }
        if(json !=null){
            all[dataIndex++] = json;
        }
        if(strValues2 != null){
            for(int i = 0; i<strValues2.length; i++){
                all[dataIndex] = strValues2[i];
                dataIndex++;
            }
        }
        return  all;
    }

    /**
     * 生成键值对
     * @param columns
     * @param values
     * @return
     */
    private static Map<String,String> intoMap(String[] columns, String[] values){
        if(columns.length != values.length){
            System.out.println(Arrays.toString(values));
        }
        Map<String,String> map = new HashMap<>();
        for(int i = 0; i < columns.length; i++){
            try{
                String cvalue = values[i];
                if(cvalue!=null &&cvalue.length()>=2){
                    cvalue = cvalue.substring(1, cvalue.length()-1);
                }
                map.put(columns[i],cvalue);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return map;
    }

    /**
     * 生成sql脚本
     *
     * @param data
     * @param dirPath
     * @throws IOException
     */
    private static void createSqlFile(Map<String, Map<String,List<String>>> data, String dirPath) throws IOException {
        Map<String,List<String>> insertList = data.get("insert");
        Map<String,List<String>> delList = data.get("delete");
        write2File(dirPath, insertList,"insert");
        //write2File(dirPath, delList,"delete");

    }

    /**
     * 将sql写入文件
     *
     *
     * @param sqls
     * @throws IOException
     */
    private static void write2File(String path, Map<String,List<String>> sqls, String type) throws IOException {
        Iterator<Map.Entry<String,List<String>>> dbs = sqls.entrySet().iterator();
        while(dbs.hasNext()){
            Map.Entry<String,List<String>> entry = dbs.next();
            String db = entry.getKey();
            String dir =  path + File.separator + db;
            File dirfile = new File(dir);
            if(!dirfile.exists()){
                dirfile.mkdirs();
            }
            String filePath = dir+File.separator+tableName+type+"sql.sql";
            File file = getFile(filePath);
            FileWriter writer = new FileWriter(file);
            BufferedWriter bfWriter = new BufferedWriter(writer);
            List<String> sqlList = entry.getValue();
            for (String sql : sqlList) {
                bfWriter.write(sql);
                //换行
                bfWriter.write("\r\n");
            }
            bfWriter.flush();
            bfWriter.close();
            writer.close();
        }
    }

    /**
     * 生成新文件
     *
     * @param path
     * @return
     * @throws IOException
     */
    private static File getFile(String path) throws IOException {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        } else {
            file.createNewFile();
        }
        return file;
    }

    /**
     * 生成sql
     *
     */
    private static String[] createInsertSql(Map<String,String> map) {
        String[] sqlInfo = new String[2];
        String dbNo = "00";
        String column = "";
        String value = "";
        Iterator<Map.Entry<String,String>> iter = map.entrySet().iterator();
        boolean first = true;
        String routerOrderId = "";
        while(iter.hasNext()){
            Map.Entry<String,String> entry = iter.next();
            String key = entry.getKey();
            key = keyHandle(key);
            String columnValue = entry.getValue();
            columnValue = valueHandle(columnValue);
            if(columnValue!=null && columnValue.length()>=14 && (routerOrderId == null || routerOrderId.equals(""))){
                routerOrderId = routemap.get(columnValue.substring(0, 12));
            }
            if("ROW_ID".equals(key)){
                continue;
            }else if("ROUTE_ORDER_ID".equals(key)){
                continue;
            }/*else if("ITEM_RATES".equals(key)){
                if("".equals(columnValue)){
                    columnValue = null;
                }
            }else if("MERCHAN_PER".equals(key)){
                if("".equals(columnValue)){
                    columnValue = null;
                }
            }else if("PLATFORM_PER".equals(key)){
                if("".equals(columnValue)){
                    columnValue = null;
                }
            }*//*else if("ORDER_ID".equals(key)){//计算库号
                if(columnValue.startsWith("00")){
                    dbNo = getDbNo(columnValue);
                }else{
                    dbNo = columnValue.substring(0, 2);
                }
            }*/

            if(key !=null){
                if(first){
                    column += key;
                    if(columnValue != null){
                        value += "'" + columnValue + "'";
                    }else{
                        value += columnValue;
                    }
                    first = false;
                }else{
                    column += "," + key;
                    if(columnValue != null){
                        value += ",'" + columnValue + "'";
                    }else{
                        value += "," + columnValue;
                    }
                }
            }
        }
        if(routerOrderId == null ||routerOrderId.equals("")){
            /*System.out.println(tableName);
            System.out.println(map.toString());*/
            //System.exit(0);
        }
        column += ",ROUTE_ORDER_ID";
        value += ",'"+routerOrderId+"'";
        String sql = "INSERT INTO "+tableName+"(" + column + ") VALUES(" + value + ");";
        sqlInfo[0] = dbNo;
        sqlInfo[1] = sql;
        return sqlInfo;
    }
    private static String keyHandle(String key){
        String val = key ;
        if("\"\"".equals(val)){
            return "";
        }
        String[] keys = key.split("\"");
        for(String str : keys){
            if(str != null && !str.equals("")){
                val = str;
                break;
            }
        }
        return val;
    }
    /**
     * 值处理
     * @param value
     * @return
     */
    private static String valueHandle(String value) {
        if (value.equals("(null)")) {
            return null;
        }/*else{
            if(value!= null && !value.equals("")&& value.length()>2){
                value = value.substring(1, value.length()-1);
            }
        }*/
        return value;
    }

    /**
     * 生成SO_DATA_DIS_INFO删除sql脚本
     *
     * @param columns
     * @param values
     * @return
     */
    private static String[] createDeleteSql(String[] columns, String[] values) {
        String[] sqlInfo = new String[2];
        String dbNo = "00";
        for(int i = 0 ;i<columns.length;i++){
            String key = keyHandle(columns[i]);
            if("ROW_ID".equals(key)){
                continue;
            }/*else if("ORDER_ID".equals(key)){//计算库号
                String columnValue = valueHandle(values[i]);
                if(columnValue.startsWith("00")){
                    dbNo = getDbNo(columnValue);
                }else{
                    dbNo = columnValue.substring(0, 2);
                }
            }*/
        }
        String sql = "DELETE FROM OMSDADM."+tableName+" WHERE GLOBAL_ID = ";
        int index = 0;
        int ordIndex = 0;
        for (String column : columns) {
            index++;
            if (column != null && "GLOBAL_ID".equals(column.toUpperCase())) {
                break;
            }
        }
        for (String column : columns) {
            ordIndex++;
            if (column != null && "ROUTE_ORDER_ID".equals(column.toUpperCase())) {
                break;
            }
        }

        if (index <= values.length && ordIndex <= values.length) {
            sql += "'" + values[index - 1] + "' AND "+"ROUTE_ORDER_ID = '"+values[ordIndex-1] + "';";
        } else {
            System.out.println("删除sql 异常，" + Arrays.toString(values));
        }
        sqlInfo[0] = dbNo;
        sqlInfo[1] = sql;
        return sqlInfo;
    }


//    private static String getDbNo(String str){
//        try {
//            String db= DbUtils.getDBNoByOrderNo(str);
//            if(db.startsWith("0")){
//                String num = db.substring(1, 2);
//                int dbNo = Integer.parseInt(num) + 1;
//                return "0"+dbNo;
//            }else{
//                int dbNo = Integer.parseInt(db) + 1;
//                return ""+dbNo;
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return str;
//    }
}
